<?php
class Reale_Model_Estate_PropertyMapper
{
    /**
     * @var Zend_Db_Table_Abstract
     */
    protected $_dbTable;
    /**
     * Specify Zend_Db_Table instance to use for data operations
     * 
     * @param  Zend_Db_Table_Abstract $dbTable 
     * @return Reale_Model_Estate_PropertyMapper
     */
    public function setDbTable ($dbTable)
    {
        if (is_string($dbTable)) {
            $dbTable = new $dbTable();
        }
        if (! $dbTable instanceof Zend_Db_Table_Abstract) {
            throw new Exception('Invalid table data gateway provided');
        }
        $this->_dbTable = $dbTable;
        return $this;
    }
    /**
     * Get registered Zend_Db_Table instance
     *
     * Lazy loads Reale_Model_DbTable_Property if no instance registered
     * 
     * @return Zend_Db_Table_Abstract
     */
    public function getDbTable ()
    {
        if (null === $this->_dbTable) {
            $this->setDbTable('Reale_Model_DbTable_Property');
        }
        return $this->_dbTable;
    }
    /**
     * 
     * To save new or update existing agent's informtion.
     * @param Reale_Model_Estate_Property
     * @return Reale_Model_Estate_Property | null 
     */
    public function save (Reale_Model_Estate_Property &$property)
    {
        $specifications = $property->getSpec()->__toArray();
        if (! is_null($property->getReferer())) {
            $agent = new Reale_Model_Users_Agent(array('agent_reg_id' => $property->getReferer()));
            $property->setReferer($agent->getAgent_id());
        }
        $estatePropertyData = array_diff_key($property->__toArray(), $specifications);
        $pArray = array('property_name' => 0, 'alternate_number' => 0, 
        'owner_address' => '0', 'referer' => 0);
        $estateData = array_diff_key($estatePropertyData, $pArray);
        $id = $property->getEstate_id();
        if ($id) {
            $rowsUpdated = $this->getDbTable()->update(
            array('estate_id = ?' => $id));
            return $rowsUpdated;
        } else {
            $estateTable = new Reale_Model_DbTable_Estate();
            $specTable = new Reale_Model_DbTable_EstateSpec();
            try {
                $this->getDbTable()
                    ->getAdapter()
                    ->beginTransaction();
                //throw new Exception(var_export($estateData,true), Zend_Log::ERR);
                $estateData['estate_type'] = 'PROPERTY';
                $pKey = $estateTable->insert($estateData);
                $property->setEstate_id($pKey);
                $propertyData = array_diff_key($estatePropertyData, $estateData);
                $propertyData['estate_id'] = $pKey;
                $this->getDbTable()->insert($propertyData);
                
                $specifications['estate_id'] = $pKey;
                $specTable->insert($specifications);
                $this->getDbTable()
                    ->getAdapter()
                    ->commit();
            } catch (Exception $e) {
                $this->getDbTable()
                    ->getAdapter()
                    ->rollBack();
              throw $e;
            }
            return $property;
        }
        return null;
    }
    /**
     * 
     * Enter description here ...
     * @param Reale_Model_Estate_Property $prop
     */
    public function fetch($prop){
    	$sql = 'SELECT
				    `property`.`estate_id`
				    , `property`.`property_name`
				    , `estate_spec`.`type_id`
				    , `estate_spec`.`category_id`
				    , `estate_spec`.`bhk`
				    , `estate_spec`.`size`
				    , `estate_spec`.`estate_for`
				    , `property`.`referer`
				    , `estate_spec`.`remark`
				    , `estate_spec`.`price`
				    , `estate`.`state`
				    , `estate`.`district`
				    , `estate`.`city`
				    , `estate`.`location`
				    , `estate_spec`.`rate_per_unit`
				    , `estate`.`summary`
				FROM
				    `thesurya_portal`.`estate_spec`
				    INNER JOIN `thesurya_portal`.`estate` 
				        ON (`estate_spec`.`estate_id` = `estate`.`estate_id`)
				    INNER JOIN `thesurya_portal`.`property` 
				        ON (`property`.`estate_id` = `estate`.`estate_id`)
				WHERE (`property`.`referer` =?)';
    	$bind = array($prop->getReferer());
    	
    	$result = $this->getDbTable()
    	               ->getDefaultAdapter()
    	               ->query($sql,$bind)
    	               ->fetchAll();
    	               
    	
    	return $result;				  
    }
    
    public function searchByParams (array $basicParams, array $specParams = NULL, 
    array $brokerageParams = NULL)
    {
        $logger = Zend_Registry::get('logger');
        $basicParams['estate_type'] = 'PROPERTY';
        $select = Zend_Db_Table::getDefaultAdapter()
                        ->select()
                        ->from('estate', 
                                array('estate_id',
                                		'state', 
                                		'district', 
                                		'city', 
                                		'is_featured', 
                                		'estate_type', 
                                		'name'))
                        ->join('property',
                        		'`property`.`estate_id` = `estate`.`estate_id`',
                                array('property_name','owner_address','alternate_number','referer'));
                                
        foreach ($basicParams as $column => $value) {
            switch ($column) {
                case 'state':
                case 'district':
                case 'city':
                case 'is_featured':
                case 'estate_type':
                    $select->where("$column = ?", $value);
                    break;
                case 'name':
                    $select->where("$column like ?", $value);
                    break;
                default:
                    $logger->debug(
                    'Search criteria "' . $column . '" is not considered.');
            }
        }
        if (count($specParams)) {
            $select->join('estate_spec', 
            '`estate_spec`.`estate_id` = `estate`.`estate_id`', 
            array('type_id', 'category_id', 'bhk', 'rate_per_unit', 
            'estate_for', 'size', 'price'));
            foreach ($specParams as $column => $value) {
                switch ($column) {
                    case 'type_id':
                    case 'category_id':
                    case 'bhk':
                    case 'rate_per_unit':
                    case 'estate_for':
                        $select->where("$column = ?", $value);
                        break;
                    case 'size':
                        $factor = 10;
                        if (isset($specParams['sizeRangeFactor'])) {
                            $factor = $specParams['sizeRangeFactor'];
                        }
                        $sizeFrom = $value - (int)($value / $factor);
                        $sizeUpto = $value + (int)($value / $factor);
                        $select->where("$column between $sizeFrom and $sizeUpto");
                        break;
                    case 'price':
                        $factor = 10;
                        if (isset($specParams['priceRangeFactor'])) {
                            $factor = $specParams['priceRangeFactor'];
                        }
                        $priceFrom = $value - (int)($value / $factor);
                        $priceUpto = $value + (int)($value / $factor);
                        $select->where(
                        "$column between $priceFrom and $priceUpto");
                        break;
                    default:
                        $logger->debug('Search criteria "' . $column . '" is not considered.');
                }
            }
        }
        
        return $select->query()->fetchAll(Zend_Db::FETCH_GROUP);
    }
	
    public function fetchBasicInfo($estate_id, $moderated = NULL) {
        $select = $this->getDbTable()->getAdapter()
                    ->select()
                    ->from('estate', array('name',
                    						'email',
                    						'contact',
                                            'state',
                                            'district',
                                            'city',
                                            'location',
                                            'summary',
                                            'is_featured'))
                    ->join('property', 
                    		'`property`.`estate_id` = `estate`.`estate_id`',
                            array('property_name','alternate_number','owner_address',
                                    'referer'))
                    ->where('property.estate_id = ?',$estate_id)
                    ->where('estate_type = ?','PROPERTY');
        if ($moderated) {
            $select->where('is_moderated = 1');
        }
        return $select->query()->fetch();;
    }
    
    public function fetchSpecInfo($estate_id) {
        
        $select = $this->getDbTable()->getAdapter()
                    ->select()
                    ->from('estate_spec', array('pid',
                    						'category_id',
                    						'type_id',
                                            'bhk',
                                            'size',
                                            'price',
                                            'rate_per_unit',
                                            'estate_for',
                                            'status',
                                            'remark'))
                    ->where('estate_id = ?',$estate_id);
        return $select->query()->fetchAll();
    }   
    
}

